<?php 
require_once ("DB.php");

/**
 * Fetch one session
 * @param string $id_session
 * @return 
 */
function service_seminars_get_session_by_id($id_session) {
    $conn = getDBConnection();
    $sql_stmt = "SELECT ss.id_session, ss.title, sp.start_time, DATE_ADD(sp.start_time, INTERVAL sp.duration MINUTE) as end_time, sp.duration, sp.location, p.first_name, p.last_name, p.photo
		FROM seminars_sessions ss, seminars_profiles sp, seminars_sessions_speakers sss, people p
		WHERE ss.id_seminar = sp.id_seminar AND ss.id_session = sss.id_session AND sss.id_person = p.id_person	AND ss.id_session = " . $id_session . "
		LIMIT 1";
	
	$result = & $conn->query($sql_stmt);
	
	$session = array();
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $session['basic_info'] = $row;
		$session['optional_info'] = array();
    }

    $result->free();
	
	/* Fetch optional information*/
	
	$id_session = $session['basic_info']['id_session'];
	$sql_stmt = "SELECT profile_key, profile_value
		FROM seminars_sessions_profiles
		WHERE id_session = " . $id_session;
	
	$result = & $conn->query($sql_stmt);
	
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
		$session['optional_info'][$row['profile_key']] = $row['profile_value'];
	}
	
	$result -> free();
	
    $conn->disconnect();
	
    return $session;
}

/**
 * Fetch the content of all seesions
 * including session id, title, starttime, duration, location, speaker id
 * @return array
 */
function service_seminars_get_sessionlist ($start = 0, $end = 50) {
	$conn = getDBConnection();
	
	/** Fecht overview information*/
	$sql_stmt = "SELECT ss.id_session, ss.title, sp.start_time, DATE_ADD(sp.start_time, INTERVAL sp.duration MINUTE) as end_time, sp.location, p.first_name, p.last_name, p.photo
		FROM seminars_sessions ss, seminars_profiles sp, seminars_sessions_speakers sss, people p
		WHERE ss.id_seminar = sp.id_seminar AND ss.id_session = sss.id_session AND sss.id_person = p.id_person
		ORDER BY sp.start_time DESC
		LIMIT ?, ?";

	$result = & $conn->query($sql_stmt, array($start, $end));
	
	$set = array();
	$session = array();
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $session['basic_info'] = $row;
		$session['optional_info'] = array();
		$set[] = $session;
    }

    $result->free();
	
	/* Fetch optional information*/
	foreach($set as $session) {
		$id_session = $session['basic_info']['id_session'];
		$sql_stmt = "SELECT profile_key, profile_value
			FROM seminars_sessions_profiles
			WHERE id_session = " . $id_session;

		$result = & $conn->query($sql_stmt);
		
		while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
			$session['optional_info'][$row['profile_key']] = $row['profile_value'];
		}
		
		$result -> free();
	}
    $conn->disconnect();

    return $set;

}

?>